MySQL排序优化与工作原理 |
您所在的位置：网站首页 › mysql 优化口诀 › MySQL排序优化与工作原理 |
MySQL排序优化与工作原理
 MySQL排序是指对数据库中的数据进行排序操作。排序是一种常见的数据处理方式，可以将数据按照一定的规则进行排列，使得数据更加有序，方便查询和分析。MySQL提供了多种排序方式，包括升序排序、降序排序、多列排序等。 基本用法 ORDER BY子句用于按照指定列对结果集进行排序。列名可以是一个或多个列名，多个列名之间用逗号分隔。ASC和DESC是可选的关键字，用于指定升序或降序排序。如果不指定，默认按照升序排序。 SELECT 列1, 列2, ... FROM 表名 ORDER BY 列名 [ASC|DESC];排序方式当使用ORDER BY语句进行排序时，MySQL会尝试使用不同的方式来优化查询。 索引排序我们先看下下面的表，其中 name 是索引 CREATE TABLE `user` ( `id` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `age` int(255) DEFAULT NULL, PRIMARY KEY (`id`), KEY `nameIndex` (`name`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;当我们使用索引排序，我们用 explain 看下执行情况  Extra这个字段中的Using index表示的就是使用了索引，因为索引本身是有序的，而且查询字段本身就是索引，不需要回表查询，只需要扫描索引就行。也就是覆盖索引。 内存排序当我们使用非索引字段排序，我们再 explain 看下执行情况  Extra这个字段中的Using filesort 表示的就是需要排序,我们看下这个SQL查询语句的执行过程。 初始化sort_buffer,MySQL会给每个线程分配一块内存用于排序，称为sort_buffer从索引name找到第一个满足 name='李'条件的主键id通过主键id去主键索引取出整行， 取id、 name、 age三个字段的值， 存入sort_buffer中从索引name取下一个记录的主键id；重复步骤3、 4直到值不满足查询条件为止对sort_buffer中的数据按照字段age做快速排序；按照排序结果返回给客户端 文件排序如果要排序的数据量小于sort_buffer_size，排序就在内存中完成。 但如果排序数据量太大， 内存放不下，此时就会使用磁盘临时文件辅助排序。可以使用下面命令查看值。 mysql> show variables like '%sort_buffer_size%'; +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | innodb_sort_buffer_size | 1048576 | | myisam_sort_buffer_size | 262144 | | sort_buffer_size | 2097152 | +-------------------------+---------+ 3 rows in set (0.04 sec)那怎么确定一个排序语句是否使用了临时文件？ 首先执行计划中的Extra字段为Using filesort接着通过查看 OPTIMIZER_TRACE 的结果来确认的/* 打开optimizer_trace， 只对本线程有效 */ SET optimizer_trace='enabled=on'; /* 执行语句 */ select name,age from `user` where name='李' order by age; /* 查看 OPTIMIZER_TRACE 输出 */ SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`从number_of_tmp_files中。看到是否使用了临时文件。number_of_tmp_files=0， 表示排序可以直接在内存中完成 "filesort_summary": { "rows": 2, "examined_rows": 2, "number_of_tmp_files": 0, "sort_buffer_size": 867728, "sort_mode": "" }number_of_tmp_files表示的是，排序过程中使用的临时文件数。内存放不下时，就需要使用外部排序，外部排序一般使用归并排序算法。 MySQL将需要排序的数据分成一定的份， 每一份单独排序后存在这些临时文件中。 然后把这这些有序文件再合并成一个有序的大文件。  rowid排序那如果查询的字段很多的话， 那么sort_buffer里能够同时放下的行数很少，就不得不要分成很多个临时文件，导致排序的性能会很差。 MySQL 使用 max_length_for_sort_data(默认值=1024)的参数进行控制，它是用于排序的行数据的长度的一个参数。 如果单行的长度超过这个值，放入sort_buffer的字段， 只有要排序的age列和主键id。 如果是全字段排序，需要查询返回的字段，都放入sort buffer，根据排序字段排完，直接返回。如果是rowid排序，只是需要排序的字段放入sort buffer，然后多一次回表操作。 注意：在MySQL中如果你创建的表没有主键，或者把一个表的主键删掉了，那么InnoDB会自己生成一个长度为6字节的rowid来作为主键。  我们把max_length_for_sort_data 设置小一点试下 SET max_length_for_sort_data = 4; /* 打开optimizer_trace， 只对本线程有效 */ SET optimizer_trace='enabled=on'; /* 执行语句 */ select name,age from `user` where name='李' order by age; /* 查看 OPTIMIZER_TRACE 输出 */ SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`查看filesort_summary结果，sort_mode变成了， 表示参与排序的只有age和id这两个字段 "filesort_summary": { "memory_available": 2097152, "key_size": 9, "row_size": 9, "max_rows_per_buffer": 1129, "num_rows_estimate": 1129, "num_rows_found": 2, "num_initial_chunks_spilled_to_disk": 0, "peak_memory_used": 32784, "sort_algorithm": "std::sort", "unpacked_addon_fields": "max_length_for_sort_data", "sort_mode": "" }那如果需要排序的字段长度很大或者当不得不对text、blob列进行排序时，会怎么样呢？ 通过max_sort_length 参数用于设置排序时字符串的最大长度。如果排序的字符串长度超过了该参数设置的长度，则会被截断。该参数默认值为1024。 排序优化 降低 max_length_for_sort_data 的值，可以让 sort_buffer_size 里存放更多的需要排序的数据，以避免触发文件排序。适用于MySQL 8.0.20 之前的版本。从8.0.20开始max_length_for_sort_data已被弃用。 检查是否可以让MySQL使用索引来提高ORDER BY速度。如果不能，尝试增加 sort_buffer_size 变量值。理想情况下，该值应足够大，以使整个结果集适合排序缓冲区（以避免磁盘文件排序）。 如果存储长字符串列的值，并且增加了 max_sort_length 的值，则排序缓冲区sort_buffer 的大小也会增加，可能需要增加 sort_buffer_size。 如果Sort_merge_passes （排序算法执行的合并次数）此值很大，则应考虑增加sort_buffer_size的值。mysql> show status like "sort%"; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Sort_merge_passes | 321 | | Sort_range | 0 | | Sort_rows | 10 | | Sort_scan | 1 | +-------------------+-------+ 4 rows in set (0.00 sec) 增加 read_rnd_buffer_size 的值，以便一次读取更多行。这个参数决定了在使用 ORDER BY 时用于随机读取的缓冲区的大小。增加此值可以在排序大型结果集时提高性能。 小结其实作为开发人员其实应该更多的从 SQL 上入手，比如：使用索引排序、减少排序的数据量、使用覆盖索引、使用整型字段进行排序等等。但是理解 ORDER BY 的工作流程，以及一些参数配置，可以更便于我们进行优化。 |
【本文地址】
今日新闻 |
推荐新闻 |